import random
import re


def createlabels(lblfile, codes):
    magichex = "\xFC\x3B\x02\x00"
    flbl_r = open(lblfile, 'rb')    
    fdat = ''
    for line in flbl_r:
        fdat = fdat + line
    flbl_r.close()
    filenum = 0;
    ctr = 0;
    r1 = re.compile(magichex)
    
    flbl_w = open('c:/dummy', 'w+')
    newfile = False
    for code in codes:
        
        if code == 0:
            continue;

        if (ctr % 21) == 0:                
            flbl_w.close()
            flbl_w = open('c:/guest/labels_' + str(filenum) + '.dps', 'w+b')
            filenum = filenum + 1
            for line in fdat:
                flbl_w.write(line)
            flbl_w.seek(0)
            it = r1.finditer(fdat)
            
        ctr = ctr + 1;
        for i in range(1,6):
            try:
                if i == 1:
                    pos = it.next().start() + 8;
                else:
                    pos = it.next().start() + 4;                
                if i < 5:                    
                    #print "changing fdat"
                    flbl_w.seek(pos)
                    c = (code / (10 ** (4 - i)) % 10);
                    flbl_w.write(str(c));                    
                    flbl_w.flush()
                    
            
            except:
                #print 'error : end of label file';
                newFile = True
                break;

            if newfile:
                break;
                
    flbl_w.close()

def csv2sql(csv_file, csvsql_file, codes):
    fcsv = open(csv_file, 'r')
    fsql = open(csvsql_file, 'w')
    
    for line in fcsv:
        row = line.split(',')
        surname = row[0]
        firstname = row[1]
        try:
            max = int(row[3])
        except:
            continue;
        try:
            id = int(row[9])
        except:
            print "thrown " + row[9]
            continue;
                
        code = 0
        while code in codes:            
            code = random.randrange(1000,9999)
        codes[code] = 1

        format_sql = '\"%u\";\"%u\";\"%s\";\"%s\";\"%u\";\"%u\"\n' % (id, code, surname, firstname, max, 0)
        fsql.writelines([format_sql]);
        #print format_sql
        

    fsql.close()
    fcsv.close()                                    


if __name__ == "__main__":
    print "start"
    codes = {0 : 1}
    csv2sql('c:/guest/guestlist_hila_parents.csv', 'c:/guest/sql_guest_hila_parents.csv', codes)
    csv2sql('c:/guest/guestlist_eyal_parents.csv', 'c:/guest/sql_guest_eyal_parents.csv', codes)
    csv2sql('c:/guest/guestlist_hila.csv', 'c:/guest/sql_guest_hila.csv', codes)
    csv2sql('c:/guest/guestlist_eyal.csv', 'c:/guest/sql_guest_eyal.csv', codes)
    sortedcodes = []
    for code in codes:
        sortedcodes.insert(0, code)
    sortedcodes.sort()        
    createlabels('c:/guest/labels.dps', sortedcodes)
    print "end"
    
